IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwMasterProposalsData]'))
DROP VIEW [dbo].[vwMasterProposalsData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================================
-- Object Name: (VIEW) dbo.vwMasterProposalsData
--
-- Author:    Arthur Bianchini
--
-- Create date: 10/15/2009
--
-- Description: View to MASTER Proposal with key lookuop valoues
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/15/09     aab        Created
-- =======================================================================
CREATE VIEW [dbo].[vwMasterProposalsData]

AS	

SELECT DISTINCT
p.inst_code AS InstCode, 
p.inst_no AS ProposalNumber, 
p.prop_no AS PropNo, 
CASE inst.int_unit_code 
WHEN '1200' THEN 'MGH' 
WHEN '2200' THEN 'BWH' 
WHEN '1700' THEN 'IHP' 
WHEN '1400' THEN 'SRH' 
WHEN '1600' THEN 'MCL'
WHEN '0100' THEN 'PHS' 
WHEN '2810' THEN 'FH' 
WHEN '4100' THEN 'NWH'
ELSE ISNULL(inst.int_unit_code, '') 
END AS Institution, 
dept.int_unit_code AS DepartmentChiefCode, 
dept.unit_name AS Department, 
un.int_unit_code AS UnitChiefCode, 
propunit.unit_name AS Unit, 
RTRIM(r.lname) + ', ' + RTRIM(r.fname) + RTRIM(ISNULL(' ' + r.mi, '')) AS PrincipalInvestigator, 
RTRIM(r.lname) + RTRIM(r.fname) + RTRIM(ISNULL(' ' + r.mi, '')) AS PISearchName, 
sp.spon_code AS SponCode, 
sp.spon_name AS SponsorName, 
spo.spon_code as OriginatingSponCode, 
spo.spon_name as OriginatingSponsor, 
ps.code_desc AS ProposalStatus, 
p.prop_stat AS PropStat,
p.proj_title AS ProposalTitle
FROM         dbo.proposal AS p 
INNER JOIN		dbo.proppds AS pd 
	ON p.prop_no = pd.prop_no 
	AND p.inst_code = pd.inst_code 
	AND pd.first_pd = 1 
INNER JOIN		dbo.faculty AS r 
	ON pd.unique_id = r.unique_id
	AND p.inst_code = r.inst_code 
INNER JOIN		dbo.codetab AS pt 
	ON pt.codeID = p.prop_type 
	AND pt.inst_code = p.inst_code 
	AND pt.arr_name = 'PROP_TYPE' 
INNER JOIN		dbo.codetab AS ps 
	ON p.prop_stat = ps.codeID 
LEFT OUTER JOIN	dbo.sponspas AS sp 
	ON p.spon_code = sp.spon_code 
	AND p.inst_code = sp.inst_code 
LEFT OUTER JOIN sponspas spo 
	on p.orig_spon = spo.spon_code 
	and p.inst_code = spo.inst_code    
INNER JOIN		dbo.unit AS un 
	ON p.inst_code = un.inst_code 
	AND p.unit_code = un.unit_code 
INNER JOIN		dbo.unit AS inst 
	ON inst.inst_code = un.inst_code 
	AND LEFT(un.deptID, 4) = inst.deptID 
	AND LEN(inst.deptID) = 4 
	AND inst.int_unit_code in ('1200','1400','1700','2200','1400','2810','4100','0100')
LEFT OUTER JOIN	dbo.unit AS dept 
	ON dept.inst_code = un.inst_code 
	AND LEFT(un.deptID, 7) = dept.deptID 
	AND LEN(dept.deptID) = 7 
LEFT OUTER JOIN	dbo.unit AS propunit 
	ON propunit.inst_code = un.inst_code 
	AND LEFT(un.deptID, 10) = propunit.deptID 
	AND LEN(propunit.deptID) = 10
WHERE     (LEN(p.inst_no) = 11) 
AND (p.system = 'pt') 
AND (p.inst_code = 'partners') 
AND (pt.codeID = 'PARTNERS_P_PROP_TYPE_2')--Master

GO



